﻿using System;
using System.Collections.Generic;
using System.Text;
/*
	12/07/2012: added column to FK script after discovering about untrusted FKeys
*/
namespace SqlServerDriver
{
	class SqlServerTemplates
	{
		static SqlServerTemplates g_source = new SqlServerTemplates();
		public static SqlServerTemplates Templates { get { return g_source; } }

		// move here from main driver for more flexibility.

		// Versions
		public enum Version { UNKNOWN = 0, SQL2000, SQL2005, SQL2008, SQL2012 };

		// Version string signatures fron SELECT @@VERSION
		private static Dictionary<Version, string> versionSig = new Dictionary<Version,string> {
		    { Version.SQL2000, "8." },
		    { Version.SQL2005, "9." },
		    { Version.SQL2008, "10." },
		    { Version.SQL2012, "11." }
		}; 

		// For new versions this should keep things rolling before an official upgrade. 
		private static Version defaultVersion = Version.SQL2012;

		SqlServerTemplates()
		{
			this.Initialise();
		}

		void Initialise()
		{
			// [Specialise: wire sql to commands]
			this.templates = new Dictionary<int, Dictionary<DbView.Commands.Cmnd, string>>();
			this.templates.Add(2000, new Dictionary<DbView.Commands.Cmnd,string>());
			this.templates.Add(2005, new Dictionary<DbView.Commands.Cmnd,string>());
			this.templates.Add(2008, new Dictionary<DbView.Commands.Cmnd,string>());
			this.templates.Add(2012, new Dictionary<DbView.Commands.Cmnd,string>());
			// populate
			Dictionary<DbView.Commands.Cmnd, String> t = this.templates[2000];
			t.Add(DbView.Commands.Cmnd.Aggregate, AGGREGATE_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.Computed, COMPUTEDCOL_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.Dependencies, DEPENDENCY_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.Fields, FIELDS_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.Functions, FUNCTIONS_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.ForeignKeys, FK_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.Indexes, INDEX_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.Permissions, PERMISSIONS_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.Procedures, PROCS_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.Tables, TABLES_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.Triggers, TRIGGERS_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.Views, VIEWS_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.ViewSourceSearch, VIEWSOURCESEARCH_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.TriggerSourceSearch, TRIGGERSOURCESEARCH_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.ProcedureSourceSearch, PROCSOURCESEARCH_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.AllSourceSearch, ALLSOURCESEARCH_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.TableFieldSearch, SEARCHFIELDS_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.FunctionSourceSearch, FUNCTIONSOURCESEARCH_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.ComputedSourceSearch, COMPUTEDSOURCESEARCH_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.RowCount, COUNT_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.MinMax, MINMAX_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.Average, AVG_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.LongShort, LONGEST_SQLTEMPLATE);

			t = this.templates[2005];
			t.Add(DbView.Commands.Cmnd.Fields, FIELDS_SQLTEMPLATE_2005);
			t.Add(DbView.Commands.Cmnd.Tables, TABLES_SQLTEMPLATE_2005);
			t.Add(DbView.Commands.Cmnd.Permissions, PERMISSIONS_SQLTEMPLATE_2005);
			t.Add(DbView.Commands.Cmnd.Indexes, INDEX_SQLTEMPLATE_2005);
			t.Add(DbView.Commands.Cmnd.ForeignKeys, FK_SQLTEMPLATE_2005);
			t.Add(DbView.Commands.Cmnd.Views, VIEWS_SQLTEMPLATE_2005);
			t.Add(DbView.Commands.Cmnd.Procedures, PROCS_SQLTEMPLATE_2005);
			t.Add(DbView.Commands.Cmnd.Functions, FUNCTIONS_SQLTEMPLATE_2005);
			t.Add(DbView.Commands.Cmnd.Triggers, TRIGGERS_SQLTEMPLATE_2005);
			t.Add(DbView.Commands.Cmnd.Computed, COMPUTEDCOL_SQLTEMPLATE_2005);
			t.Add(DbView.Commands.Cmnd.Dependencies, DEPENDENCY_SQLTEMPLATE_2005);
			t.Add(DbView.Commands.Cmnd.TableFieldSearch, SEARCHFIELDS_SQLTEMPLATE_2005);
			t.Add(DbView.Commands.Cmnd.ViewSourceSearch, VIEWSOURCESEARCH_SQLTEMPLATE_2005);
			t.Add(DbView.Commands.Cmnd.TriggerSourceSearch, TRIGGERSOURCESEARCH_SQLTEMPLATE_2005);
			t.Add(DbView.Commands.Cmnd.ProcedureSourceSearch, PROCSOURCESEARCH_SQLTEMPLATE_2005);
			t.Add(DbView.Commands.Cmnd.AllSourceSearch, ALLSOURCESEARCH_SQLTEMPLATE_2005);
			t.Add(DbView.Commands.Cmnd.FunctionSourceSearch, FUNCTIONSOURCESEARCH_SQLTEMPLATE_2005);


		}


		
		public String FetchTemplate(int sqlServerVersion, DbView.Commands.Cmnd command)
		{
			// have had mapping problesms here before
			if (!this.templates.ContainsKey(sqlServerVersion))
				throw new ApplicationException(String.Format("Cannot locate template resource for Sql Server version {0}", sqlServerVersion));
			if (this.templates[sqlServerVersion].ContainsKey(command))
				return this.templates[sqlServerVersion][command];
			if (sqlServerVersion == 2000) return ""; // can't help
			sqlServerVersion = (sqlServerVersion == 2012) ? 2008 :
						 (sqlServerVersion == 2008) ? 2005 :
			             (sqlServerVersion == 2005) ? 2000 : 2000;
			return FetchTemplate(sqlServerVersion, command);
		}

		// Sorry about the API parameter confusion. Todo: tidy
		public static String FetchTemplate(Version sqlServerVersion, DbView.Commands.Cmnd command)
		{
			int versionNumber = MapVersion(sqlServerVersion);
			return new SqlServerTemplates().FetchTemplate(versionNumber, command);
		}

		// Sorry about the API parameter confusion. Todo: tidy
		public static String AltTableTemplate(int versionNumber)
		{
			// I've no fallback strategy if sql 2000 tables command fails
			if (versionNumber == 2000) return TABLES_SQLTEMPLATE;
			// here if no permission to access the sys table with rowcount information
			return TABLES_SQLTEMPLATE_2005_NOROWS;
		}
		// Sorry about the API parameter confusion. Todo: tidy
		public static String AltAggregateTemplate(int versionNumber)
		{
			// here if no permission to access the sys table with rowcount information
			return AGGREGATE_SQLTEMPLATE_ALT;
		}

		public static int MapVersion(Version sqlVersion)
		{
			// get version from eg SQL2008
			// SQL2008  -> 2008
			string s = sqlVersion.ToString();
			s = s.Replace("SQL","");
			int version;
			if (!int.TryParse(s, out version))
				return -1;
			return version;
		}
		
		// param expected to come from SELECT SERVERPROPERTY('ProductVersion')
		// Hm not supported in 2000 I don't think.
		public static Version DetermineSqlVersion(String versionInfo)
		{
			foreach (Version v in versionSig.Keys)
			{
				if (versionInfo.IndexOf(versionSig[v]) == 0)
					return v;
			}
			// radical: return the default. So don't try this on SQL97
			return defaultVersion;
		}


		public String FetchExtensionTemplate(int sqlServerVersion, DbView.Command command, String extensionCommandTag)
		{
			if (SqlServerBrokerPopupBuilder.IsBrokerCommand(command) && sqlServerVersion > 2000)  // SQL Server 2000 does not support service broker.
				return BrokerTemplates.GetBrokerTemplate(command);
			// [Specialise: wire extension command sql to commands]
			switch (extensionCommandTag.ToUpper())
			{
				case Cmd.TableSizes: return TABLE_SIZE_TEMPLATE;
				case Cmd.Locks : return LOCKS_SQLTEMPLATE;
				case Cmd.Processes: return PROCESSES_SQLTEMPLATE;
				case Cmd.Owners: return OWNERS_SQLTEMPLATE_2005;
				case Cmd.Jobs : return JOBS_SQLTEMPLATE;
				case Cmd.JobSteps : return JOBSTEPS_SQLTEMPLATE;
				case Cmd.JobHistory : return JOBHISTORY_SQLTEMPLATE;
				case Cmd.Descriptions: return FIELDDESCRIPTIONS_TEMPLATE;
				// could test whether replication is enabled for this database
				case Cmd.Publications: return PUBLICATIONS_TEMPLATE;
				case Cmd.Articles: return REPLARTICLES_TEMPLATE;
				case Cmd.DisplayHeldTable: return "-- Nothing";
				// unsupported by sql 2000
				case Cmd.ServiceBroker:  return (sqlServerVersion > 2000) ? SERVICEBROKER_SQLTEMPLATE : "";
				case Cmd.DependencyTree: return (sqlServerVersion > 2000) ? DEPENDTREE_TEMPLATE : "";
				case Cmd.Users: return (sqlServerVersion > 2000) ? USERS_TEMPLATE : "";
				case Cmd.LinkedServers: return (sqlServerVersion > 2000) ? LINKEDSERVER_TEMPLATE : "";
				case Cmd.ExtendedProperties: return (sqlServerVersion > 2000) ? PROPSUMMARY_TEMPLATE : "";
				case Cmd.PropertyValues: return (sqlServerVersion > 2000) ? ExtendedPropertiesTemplates.GetExtendedPropertiesTemplate(command) : "";

			}
			return "";
		}

		public static String SourceCodeAccessPermissionSql()
		{
			// not for 2000; hopefully OK for 2005.
			return "if exists(select 1 from fn_my_permissions(NULL, 'DATABASE') where permission_name='VIEW DEFINITION') select 1 else select 0";

		}

		//private string MakeQuickDiffTemplate(DbView.Command command)
		//{
		//    // parameters for which 
		//    return "";
		//}

		// process [[ ]] filter clause
		// 
		public String ProcessFilterClause(String clause, String filterText, bool isCS)
		{
            return this.ProcessFilterClause(clause, "[", filterText, isCS);
		}

		public String ProcessFilterClause(String clause, String delimeter, String filterText, bool isCS)
		{
			String seekSt = ClauseStart(delimeter);
			String seekEnd = ClauseEnd(delimeter);
			int pos1 = clause.IndexOf(seekSt);
			int pos2 = clause.IndexOf(seekEnd);
			// if [[ ]] not present nothing to do
			if (pos1 == -1 || pos2 == -1 || pos2 < pos1) return clause;
			// 
			pos2 += 2; // advance to end
			String filterClause = clause.Substring(pos1, (pos2 - pos1));
			if (filterText.Length == 0)
			{
				return this.ProcessFilterClause(clause.Replace(filterClause, ""), filterText, isCS);
			}
			// make comparison text u/c if database is case sensitive.
			String usedFilterText = (isCS) ? filterText.ToUpper() : filterText;
			// escape '
			usedFilterText = usedFilterText.Replace("'", "''");
			// Check filter text for % and remove default
			if (filterText.IndexOf('%') != -1)
				filterClause = filterClause.Replace("'%%'", "'"+usedFilterText+"'");
			else
				filterClause = filterClause.Replace("'%%'", "'%"+usedFilterText+"%'");
			// check clause for _ and remove REPLACE()
			if (filterText.IndexOf("_") != -1)
			{
				filterClause = filterClause.Replace("REPLACE(","")    //
				               .Replace(",'_','')", "");  //
			}
			// if case insensitive remove UPPER()
			if (!isCS)
			{
				filterClause = filterClause.Replace("UPPER(","")    //
				               .Replace(") like", " like");  //
			}
			
			String x = clause.Substring(0, pos1) + 
			           filterClause.Replace(seekSt,"").Replace(seekEnd, "") + 
			           clause.Substring(pos2);
			
			// process more than one clause
			return this.ProcessFilterClause(x, delimeter, filterText, isCS);
		}



		private static string ClauseStart(string delimeter)
		{
			return delimeter + delimeter; // [ -> [[
		}

		private static string ClauseEnd(string delimeter)
		{
			String delend = (delimeter == "{") ? "}" :
			                (delimeter == "[") ? "]" : "]"; // add extras if needed.
			return delend + delend; // { -> }}
		}

		Dictionary<int, Dictionary<DbView.Commands.Cmnd, String>> templates;

		const String TABLES_SQLTEMPLATE = @"
select name
  from sysobjects o
 where xtype in ('U') 
   [[and UPPER(REPLACE(name,'_','')) like '%%']]
 order by name
";

		const String TABLES_SQLTEMPLATE_2005 = @"
select Name, Rows, LastUpdate as [Last Update], CreateDate as [Created Date], 
					case when Datediff(mi, CreateDate, LastChangeDate) < 10 -- ten minutes is a cut-off for display
						 then NULL else LastChangeDate end [Last Changed Date]
 from ( select case when sc.name='dbo' then o.name 
		 			else sc.name+'.'+o.name end as Name, 
				s.rows as Rows,
				u.last_user_update as LastUpdate,
				o.create_date as CreateDate,
				o.modify_date as LastChangeDate,
				case when sc.name='dbo' then 0 else 1 end as isDbo
			 from sys.objects o
		left join sys.schemas sc on sc.schema_id = o.schema_id
		left join (select object_id, sum(rows) as rows     
					from sys.partitions                        
					where index_id < 2            -- only 0 or 1. Others will double-count
				 group by object_id) s on o.object_id = s.object_id
		left join (select database_id, object_id, -- these two fields for object identification
							max(last_user_update) as last_user_update   -- take the most recent update
					from sys.dm_db_index_usage_stats 
					where index_id < 2         -- only 0 or 1. Others are not necessary
				 group by database_id,object_id
				) u on u.object_id = o.object_id and u.database_id = DB_ID()
		    where type in ('U') 
		) q
   [[where UPPER(REPLACE(Name,'_','')) like '%%']]
  order by isDbo, Name
";
// hem. not norows no more. now not update date
		const String TABLES_SQLTEMPLATE_2005_NOROWS = @"
select Name, Rows
 from ( select case when sc.name='dbo' then o.name 
		 			else sc.name+'.'+o.name end as Name, 
				s.rows as Rows,
				case when sc.name='dbo' then 0 else 1 end as isDbo
			 from sys.objects o
		left join sys.schemas sc on sc.schema_id = o.schema_id
		left join (select object_id, sum(rows) as rows     
					from sys.partitions                        
					where index_id < 2            -- only 0 or 1. Others will double-count
				 group by object_id) s on o.object_id = s.object_id
		    where type in ('U') 
		) q
   [[where UPPER(REPLACE(Name,'_','')) like '%%']]
  order by isDbo, Name
";

		const String OWNERS_SQLTEMPLATE_2005 = @"
    select  s.name as [OwnerName], 
			case when p1.principal_id = s.principal_id then 'Principal' else 'Schema' end as [OwnerType],
			isnull(Tables, 0) as Tables,
			isnull(Views, 0) as Views, 
			isnull(Procedures, 0) as Procedures,
			isnull(Functions, 0) as Functions
      from sys.schemas s
      join sys.database_principals p on p.principal_id = s.principal_id
 left join sys.database_principals p1 on p1.name = s.name
 left join (select sc.schema_id, 
			       SUM(case when o.type='U' then 1 else 0 end) as Tables,
			       SUM(case when o.type='V' then 1 else 0 end) as Views,
			       SUM(case when o.type='P' then 1 else 0 end) as Procedures,
			       SUM(case when o.type in ('FN','TF') then 1 else 0 end) as Functions
			 from sys.objects o
			join sys.schemas sc on sc.schema_id = o.schema_id
			 left join sys.database_principals p1 on p1.name = sc.name
			     where p1.principal_id is null or 1=1
			group by sc.schema_id
			) agg on agg.schema_id = s.schema_id
     where (p1.principal_id is null or (Tables + Views + Procedures + Functions) > 0)
   [[and UPPER(REPLACE(s.name,'_','')) like '%%']]
  order by case when p1.principal_id is null then 0 else 1 end, s.name
";

		const String VIEWS_SQLTEMPLATE = @"
select name as [View Name] from sysobjects
 where xtype in ('V') 
   [[and UPPER(REPLACE(name,'_','')) like '%%']]
 order by name
";

        const String VIEWS_SQLTEMPLATE_2005 = @"
select Name as [View Name], 
				Lines, 
				Chars as [Size],
				modified as [Last Changed]
 from ( select case when sc.name='dbo' then o.name 
		 			else sc.name+'.'+o.name end as Name, 
				len(m.definition) as Chars,
				len(m.definition) - len(replace(m.definition, CHAR(10),''))+1 as Lines, -- may be too slow
				isnull(modify_date,create_date) as modified,
				case when sc.name='dbo' then 0 else 1 end as isDbo
			 from sys.objects o
		left join sys.schemas sc on sc.schema_id = o.schema_id
		left join sys.sql_modules  m on m.object_id = o.object_id
		    where type in ('V') 
		) q
   [[where UPPER(REPLACE(Name,'_','')) like '%%']]
  order by isDbo, Name
";

// replace above with this to simulate a long query for threading testing.
//        const String VIEWS_SQLTEMPLATE_2005 = @"
//BEGIN
//    WAITFOR DELAY '00:00:10';
//    select @@VERSION
//END
////";

		const String PROCS_SQLTEMPLATE = @"
select name as [Procedure Name] from sysobjects
 where xtype in ('P') 
   [[and UPPER(REPLACE(name,'_','')) like '%%']]
 order by name
";

		const String PROCS_SQLTEMPLATE_2005 = @"
select Name as [Procedure Name], 
				-- Lines, 
				Chars as [Size],
				modified as [Last Changed]
 from ( select case when sc.name='dbo' then o.name 
		 			else sc.name+'.'+o.name end as Name, 
                len(m.definition) as Chars,
				--len(m.definition) - len(replace(m.definition, CHAR(10),''))+1 as Lines, -- too slow
				isnull(modify_date,create_date) as modified,
				case when sc.name='dbo' then 0 else 1 end as isDbo
			 from sys.objects o
		left join sys.schemas sc on sc.schema_id = o.schema_id
		left join sys.sql_modules  m on m.object_id = o.object_id
		    where type in ('P') 
		) q
   [[where UPPER(REPLACE(Name,'_','')) like '%%']]
  order by isDbo, Name
";

		const String FIELDS_SQLTEMPLATE_2005 = @"
select     -- case when sc.name='dbo' then t.name else sc.name+'.'+t.name end as [Table Name], 
           cols.name as [Field Name],
           type_name(cols.user_type_id) as [Type], 
           case when cols.max_length = -1 then 'MAX' 
                else convert(varchar(20), case when cols.system_type_id in (231,239) then cols.max_length/2 
                                               when cols.system_type_id in (59,60,62,106,108) then cols.precision
                                               else cols.max_length end) end as [Length],
           case when cols.system_type_id in (59,60,62,106,108) then cols.scale else null end [Dp],
           case cols.is_nullable when 0 then 'No' else '' end as [Nulls?], 
           case when ind.is_primary_key = 1 then 'Yes' else '' end as [PKey?],
           case when cols.is_identity = 1 then '('+LTRIM(STR(ISNULL(IDENT_SEED(cols.object_id),1)))+','
                                                  +LTRIM(STR(ISNULL(IDENT_INCR(cols.object_id),1)))+')' 
                                          else '' end as [Auto?],
           case when ind.is_non_pk_index = 0 then 'Yes' else '' end as [Index?],
           case when fkey.parent_object_id is not null then 'Yes' else '' end as [FKey?],
           case when c.definition is not null then 'Yes' else '' end as [Def?],
           case cols.is_computed when 1 then 'Yes' else '' end as [Calc?],
           coalesce(c.definition, def.definition,'') as [Default/Computed]
  from sys.columns cols
  -- join sys.tables t on cols.object_id = t.object_id
  -- left join sys.schemas sc on sc.schema_id = t.schema_id
  left join (select ic.object_id, ic.column_id, 
                    max(cast(is_primary_key as tinyint)) as is_primary_key, 
                    min(cast(is_primary_key as tinyint)) as is_non_pk_index
               from sys.index_columns ic 
               join sys.indexes ind on ind.object_id = ic.object_id and ind.index_id = ic.index_id 
               group by ic.object_id, ic.column_id) ind on ind.object_id = cols.object_id  and ind.column_id = cols.column_id
  left join (select distinct parent_object_id, parent_column_id 
               from sys.foreign_key_columns) fkey on fkey.parent_object_id = cols.object_id 
                                                 and fkey.parent_column_id = cols.column_id
  left join sys.default_constraints c on c.parent_object_id = cols.object_id  and c.parent_column_id = cols.column_id 
  left join sys.computed_columns def on def.object_id = cols.object_id  and def.column_id = cols.column_id
      where cols.object_id = OBJECT_ID('{0}') -- remove this clause to show or search all tables
   [[and UPPER(cols.name) like '%%']] 
  order by -- case when sc.name='dbo' then '    .'+t.name else sc.name+'.'+t.name end,
			cols.column_id
"; 
////      and UPPER(REPLACE(cols.name,'_','')) like '%{1}%'

		const String FIELDS_SQLTEMPLATE = @"
if object_id('tempdb..#fields') is not null drop table #fields
if object_id('tempdb..#pkeys') is not null drop table #pkeys
if object_id('tempdb..#fkeys') is not null drop table #fkeys
if object_id('tempdb..#auto') is not null drop table #auto
if object_id('tempdb..#index') is not null drop table #index

select cu.COLUMN_NAME
    into #pkeys
    from INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
    join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc on tc.TABLE_NAME = cu.TABLE_NAME and tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
   where cu.TABLE_NAME = '{0}'
     and tc.CONSTRAINT_TYPE='PRIMARY KEY'

select cu.COLUMN_NAME, pk.TABLE_NAME
    into #fkeys
    from INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
    join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc on tc.TABLE_NAME = cu.TABLE_NAME and 
                                              tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
    join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS fk on tc.CONSTRAINT_NAME = fk.CONSTRAINT_NAME
    join INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk on pk.CONSTRAINT_NAME = fk.UNIQUE_CONSTRAINT_NAME
   where cu.TABLE_NAME = '{0}'
     and tc.CONSTRAINT_TYPE='FOREIGN KEY'

create table #index (index_name sysname, descr varchar(512), keys varchar(512))
insert into #index (index_name, descr, keys) exec sp_helpindex '{0}'

   SELECT cu.COLUMN_NAME, 
          cu.DATA_TYPE, 
          cu.ORDINAL_POSITION,
          cu.CHARACTER_MAXIMUM_LENGTH,
		  IS_NULLABLE,
          cast(NULL As char(3)) as CALC,
          cast(NULL As char(3)) as IDX,
          COLUMN_DEFAULT
     into #fields
   	 FROM INFORMATION_SCHEMA.COLUMNS cu
    where cu.TABLE_NAME = '{0}'
 order by cu.ORDINAL_POSITION

   update f set IDX = 'YES'
     from #fields f
left join #pkeys pk on pk.COLUMN_NAME = f.COLUMN_NAME
     where exists (select * from #index where keys like '%'+f.COLUMN_NAME+'%' collate database_default)


   update f set CALC = 'YES', COLUMN_DEFAULT = definition
     from #fields f
     join (SELECT name as COLUMN_NAME,
                  text as definition
              FROM syscolumns cl 
              JOIN syscomments cm ON cl.id = cm.id AND cm.number = cl.colid
             WHERE iscomputed = 1 
               and object_name(cl.id)='TheData') c on c.COLUMN_NAME = f.COLUMN_NAME

select COLUMN_NAME, IDENT_SEED('{0}') as SEED ,IDENT_INCR('{0}') as INC
  into #auto
  from INFORMATION_SCHEMA.COLUMNS
 where columnproperty(object_id(quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1
   and TABLE_NAME='{0}'

select f.COLUMN_NAME as [Field Name], 
       f.DATA_TYPE as [Type],
	   f.CHARACTER_MAXIMUM_LENGTH as [Length],
	   case when LEFT(f.IS_NULLABLE,1) = 'N' then 'NO' end as [Nulls?],
       case when pk.COLUMN_NAME is NULL then NULL else 'YES' END as [PKey?],
       case when a.COLUMN_NAME is not null then '('+LTRIM(STR(SEED))+','+LTRIM(STR(INC))+')' end as [Auto?],
       f.IDX as [Index?],
       fk.TABLE_NAME as [FKey?],
       f.CALC as [Calc?],
	   f.COLUMN_DEFAULT as [Default/Computed]
  from #fields f
left join #pkeys pk on pk.COLUMN_NAME = f.COLUMN_NAME
left join #fkeys fk on fk.COLUMN_NAME = f.COLUMN_NAME
left join #auto a on a.COLUMN_NAME = f.COLUMN_NAME
 [[where UPPER(f.COLUMN_NAME) like '%%']] 
order by ORDINAL_POSITION
";

//////////////////////////////////////
// random SQL originally from elsewhere
		public const String FIELDS_SQLTEMPLATE_LITE = @"
   select cu.COLUMN_NAME as [Field Name], 
          cu.DATA_TYPE as [Type],
	      cu.CHARACTER_MAXIMUM_LENGTH as [Length]
   	 FROM INFORMATION_SCHEMA.COLUMNS cu
    where cu.TABLE_NAME = '{0}'
      and cu.TABLE_SCHEMA = case when LEN('{1}') = 0 then 'DBO' else '{1}' end
 order by cu.ORDINAL_POSITION
";

		public const String PROPERTIES_SQL = @"
SELECT SERVERPROPERTY('ProductVersion'),
       SERVERPROPERTY('Collation'),
       case when charindex(cast(DATABASEPROPERTYEX(DB_NAME(), 'Collation') as varchar(120)), '_CS_') > 0 then 'Y' 
            when UPPER(cast(DATABASEPROPERTYEX(DB_NAME(), 'Collation') as varchar(120))) = 'LATIN1_GENERAL_BIN' then 'Y'
            else 'N' end as [casesensitive]
";

//////////////////////////////////////
// source search family (2000)
		const String VIEWSOURCESEARCH_SQLTEMPLATE = @"
SELECT distinct so.name as [Name]
FROM sysobjects so INNER JOIN syscomments sc ON so.id = sc.id 
WHERE xtype='V' and UPPER(sc.text) like UPPER('%{0}%')
[[and UPPER(so.name) like '%%']] ORDER BY so.name
";
		const String PROCSOURCESEARCH_SQLTEMPLATE = @"
SELECT distinct so.name as [Name]
FROM sysobjects so INNER JOIN syscomments sc ON so.id = sc.id 
WHERE xtype='P' and UPPER(sc.text) like UPPER('%{0}%')
[[and UPPER(so.name) like '%%']]
ORDER BY so.name
";
		const String FUNCTIONSOURCESEARCH_SQLTEMPLATE = @"
SELECT distinct so.name as [Name]
FROM sysobjects so INNER JOIN syscomments sc ON so.id = sc.id 
WHERE xtype in ('FN','TF') and UPPER(sc.text) like UPPER('%{0}%')
[[and UPPER(so.name) like '%%']]
ORDER BY so.name
";
		const String TRIGGERSOURCESEARCH_SQLTEMPLATE = @"
SELECT distinct so.name as [Name]
FROM sysobjects so INNER JOIN syscomments sc ON so.id = sc.id 
WHERE xtype='TR' and UPPER(sc.text) like UPPER('%{0}%')
[[and UPPER(so.name) like '%%']]ORDER BY so.name
";
		const String ALLSOURCESEARCH_SQLTEMPLATE = @"
SELECT distinct so.name as [Name],
		xtype as Type,
		sc.text as SourceCode
FROM sysobjects so INNER JOIN syscomments sc ON so.id = sc.id 
WHERE UPPER(sc.text) like UPPER('%{0}%')
[[and UPPER(so.name) like '%%']]
ORDER BY so.name
";

		const String VIEWSOURCESEARCH_SQLTEMPLATE_2005 = @"
select distinct * from 
( SELECT case when s.name = 'dbo' then so.name else s.name+'.'+so.name end as [Name]
    FROM sys.objects so 
    JOIN sys.schemas s on s.schema_id = so.schema_id
    JOIN syscomments sc ON so.object_id = sc.id 
   WHERE type='V' and UPPER(sc.text) like UPPER('%{0}%')
) sq 
[[where UPPER(sq.Name) like '%%']] ORDER BY sq.Name
";
		const String PROCSOURCESEARCH_SQLTEMPLATE_2005 = @"
select distinct * from 
( SELECT case when s.name = 'dbo' then so.name else s.name+'.'+so.name end as [Name]
    FROM sys.objects so 
    JOIN sys.schemas s on s.schema_id = so.schema_id
    JOIN syscomments sc ON so.object_id = sc.id 
   WHERE type='P' and UPPER(sc.text) like UPPER('%{0}%')
) sq 
[[where UPPER(sq.Name) like '%%']] ORDER BY sq.Name
";
		const String FUNCTIONSOURCESEARCH_SQLTEMPLATE_2005 = @"
select distinct * from 
( SELECT case when s.name = 'dbo' then so.name else s.name+'.'+so.name end as [Name]
    FROM sys.objects so 
    JOIN sys.schemas s on s.schema_id = so.schema_id
    JOIN syscomments sc ON so.object_id = sc.id 
   WHERE type in ('FN','TF') and UPPER(sc.text) like UPPER('%{0}%')
) sq 
[[where UPPER(sq.Name) like '%%']] ORDER BY sq.Name
";
		const String TRIGGERSOURCESEARCH_SQLTEMPLATE_2005 = @"
select distinct * from 
( SELECT case when s.name = 'dbo' then so.name else s.name+'.'+so.name end as [Name]
    FROM sys.objects so 
    JOIN sys.schemas s on s.schema_id = so.schema_id
    JOIN syscomments sc ON so.object_id = sc.id 
   WHERE type = 'TR' and UPPER(sc.text) like UPPER('%{0}%')
) sq 
[[where UPPER(sq.Name) like '%%']] ORDER BY sq.Name
";

		const String COMPUTEDSOURCESEARCH_SQLTEMPLATE = @"
select 
	Object_Name(com.id) as [Table name],
	col.[name] as [Column name],
	len(com.text) as [Len], 
	com.text as Calculation 
from syscomments com 
join syscolumns col on com.id = col.id and com.number = col.colid 
join sysobjects obj on obj.id = com.id 
where col.iscomputed = 1 and obj.xtype='U'
[[and (UPPER(com.text) like '%%')]]
"; // hey I'm leaving this bug for posterity.

// In case the new version does not work with 2005
//const String ALLSOURCESEARCH_SQLTEMPLATE_2005=@"
//SELECT case when s.name = 'dbo' then so.name else s.name+'.'+so.name end as [Name],
//	   t.name as ObjectType, 
//	   sc.text as SourceCode
//    FROM sys.objects so 
//    JOIN sys.schemas s on s.schema_id = so.schema_id
//    JOIN syscomments sc ON so.object_id = sc.id 
//	LEFT JOIN (SELECT LEFT(name,2) as Code, 
//					   Substring(name, charindex(':',name)+2, len(name)) as name
//				 FROM  master.dbo.spt_values 
//				WHERE type ='O9T ' and number = -1
//				) t on t.Code = so.type collate database_default
//   WHERE /* type='P' and */ UPPER(sc.text) like UPPER('%{0}%')
//  [[and UPPER(s.name) like '%%']]
//   order by type, case when s.name = 'dbo' then so.name else s.name+'.'+so.name end
//";

const String ALLSOURCESEARCH_SQLTEMPLATE_2005=@"
select case when s.name = 'dbo' then so.name else s.name+'.'+so.name end as [Name],
       case so.type when 'U' then 'column: '+ com.col else t.name end as ObjectType, 
       com.text
    -- object and type
    from sys.objects so 
    join sys.schemas s on s.schema_id = so.schema_id
    join (select LEFT(name,2) as Code, 
                    Substring(name, charindex(':',name)+2, len(name)) as name
                from master.dbo.spt_values 
            where type ='O9T ' and number = -1
            ) t on t.Code = so.type collate database_default
    join 
    (
        -- view / sp / function / trigger source
        select com.id, null as col, com.text
          from sys.objects so
          join sys.syscomments com on so.object_id = com.id
         where so.type != 'U' -- exclude computed column text (see below)
        union
        -- computed column text
        select so.object_id, com.name, com.definition
          from sys.objects so
          join sys.columns cols on cols.object_id = so.object_id and so.type = 'U'
          join sys.computed_columns com on com.object_id = cols.object_id and com.column_id = cols.column_id
    ) com on com.id = so.object_id
    where /* type='P' and */ UPPER(com.text) like UPPER('%{0}%')
    [[and UPPER(s.name) like '%%']]
    order by type, case when s.name = 'dbo' then so.name else s.name+'.'+so.name end
";

		const String SEARCHFIELDS_SQLTEMPLATE = @"
SELECT o.name as TABLE_NAME, c.name as FIELD_NAME
  FROM syscolumns c
  join sysobjects o on o.id = c.id
 WHERE o.xtype in ('U') and {{UPPER(REPLACE(c.name,'_','')) like '%%'}} -- Searching for {0}
[[and UPPER(REPLACE(o.name,'_','')) like '%%']] 
order by o.name";

		const String SEARCHFIELDS_SQLTEMPLATE_2005 = @"
select TABLE_NAME, FIELD_NAME 
 from ( select case when sc.name='dbo' then o.name 
		 			else sc.name+'.'+o.name 
                    end as TABLE_NAME, 
               c.name as FIELD_NAME,
               c.column_id
			 from sys.objects o
             join sys.columns c on c.object_id = o.object_id
		left join sys.schemas sc on sc.schema_id = o.schema_id
		    where type in ('U') 
		) q
 WHERE 1=1 {{ and UPPER(REPLACE(FIELD_NAME,'_','')) like '%%'}} -- Searching for {0}
[[and UPPER(REPLACE(TABLE_NAME,'_','')) like '%%']] 
order by TABLE_NAME, column_id
";

		const String FUNCTIONS_SQLTEMPLATE = @"
SELECT name as [Function Name] FROM sysobjects 
WHERE xtype in ('FN','TF') [[AND UPPER(REPLACE(name,'_','')) like '%%']]
order by UPPER(REPLACE(name,'_',''))
";
		const String FUNCTIONS_SQLTEMPLATE_2005 = @"
select case when sc.name='dbo' then o.name else sc.name+'.'+o.name end as [Function Name], 
		len(m.definition) - len(replace(m.definition, CHAR(10),''))+1 as Lines, -- may be too slow
		len(m.definition) as [Size],
		isnull(modify_date, create_date) as [Last Changed]
  from sys.objects o
  left join sys.schemas sc on sc.schema_id = o.schema_id
  left join sys.sql_modules  m on m.object_id = o.object_id
 where type in ('FN','TF') 
   [[and (UPPER(REPLACE(o.name,'_','')) like '%%' or UPPER(REPLACE(sc.name,'_','')) like '%%')]]
  order by case when sc.name='dbo' then '' else sc.name end, o.name
";

		const String TRIGGERS_SQLTEMPLATE = @"
SELECT name as [Trigger name]
  FROM sysobjects 
 WHERE xtype = 'TR' [[AND UPPER(REPLACE(name,'_','')) like '%%']]
order by UPPER(REPLACE(name,'_',''))
";

		const String TRIGGERS_SQLTEMPLATE_2005 = @"
select case when sc.name='dbo' then o.name else sc.name+'.'+o.name end as [Trigger name],
       case when sc.name='dbo' then tb.name else sc.name+'.'+tb.name end as [Table],	
       len(m.definition) - len(replace(m.definition, CHAR(10),''))+1 as Lines, -- may be too slow
       len(m.definition) as [Size],
       case t.is_disabled when 1 then 'Y' else '' end as [Disabled?]
  from sys.objects o
  join sys.triggers t on t.object_id = o.object_id
  left join sys.schemas sc on sc.schema_id = o.schema_id
  left join sys.sql_modules  m on m.object_id = o.object_id
  left join sys.tables tb on tb.object_id = o.parent_object_id
 where o.type in ('TR') 
   [[and (UPPER(REPLACE(o.name,'_','')) like '%%' 
          or UPPER(REPLACE(sc.name,'_','')) like '%%'
          or UPPER(REPLACE(tb.name,'_','')) like '%%')]]
  order by 2, 1
";


		const String COMPUTEDCOL_SQLTEMPLATE = @"
select Object_Name(com.id) as [Table name], 
		col.[name] as [Column name], 
		len(com.text) as [Len], 
		com.text as Calculation 
 from syscomments com 
 join syscolumns col on com.id = col.id and com.number = col.colid 
 join sysobjects obj on obj.id = com.id 
where col.iscomputed = 1 and obj.xtype='U'
  [[and (UPPER(com.text) like '%%']] 
  [[or UPPER(Object_Name(com.id)) like '%%']]
  [[or UPPER(col.[name]) like '%%')]]
";

		const String COMPUTEDCOL_SQLTEMPLATE_2005 = @"
select TableName, 
		cols.[name] as [Column name], 
		len(com.definition) as [Len], 
		com.definition as Calculation 
 from (select o.object_id,
			  o.type, 
              case when s.name = 'dbo' 
				   then o.name 
                   else s.name+'.'+o.name end as TableName,
              case when s.name = 'dbo' 
				   then 1
                   else 0 end as isDbo
	    from sys.objects o
   left join sys.schemas s on s.schema_id = o.schema_id
	  ) o
 join sys.columns cols on cols.object_id = o.object_id
 join sys.computed_columns com on com.object_id = cols.object_id and com.column_id = cols.column_id
where cols.is_computed = 1 and o.type='U'
  [[and (UPPER(com.definition) like '%%']] 
  [[or UPPER(TableName) like '%%']]
  [[or UPPER(cols.[name]) like '%%')]]
order by isDbo desc, TableName, cols.column_id
";

	const String FK_SQLTEMPLATE = @"
select convert(sysname,o2.name) as TABLE_WITH_FK,
        convert(sysname,c2.name) as FK_FIELD,
        convert(sysname,o1.name) as PK_TABLE,
        convert(sysname,c1.name) as PKEY,
        OBJECT_NAME(k.constraint_object_id) as FK_NAME,
        case f.is_disabled when 1 then 'Y' else '' end as DISABLED,
        case f.delete_referential_action when 0 then 'NO ACTION' WHEN 1 THEN 'CASCADE' WHEN 2 THEN 'SET_NULL' end as DELETE_RULE,
        case f.update_referential_action when 0 then 'NO ACTION' WHEN 1 THEN 'CASCADE' WHEN 2 THEN 'SET_NULL' end as UPDATE_RULE,
        case f.is_not_for_replication when 1 then 'Y' else 'N' end as REPLICATION_SAFE
 from   sys.foreign_keys f 
 join   sys.foreign_key_columns k on k.constraint_object_id = f.object_id
 join   sys.objects o1 on o1.object_id = f.referenced_object_id
 join   sys.objects o2 on o2.object_id = f.parent_object_id
 join   sys.columns c1 on c1.object_id = f.referenced_object_id and 
                          c1.column_id = k.referenced_column_id
 join   sys.columns c2 on c2.object_id = f.parent_object_id and 
                          c2.column_id = k.parent_column_id
	 [[ where (UPPER(o2.name) like '%%' 
		or UPPER(c2.name) like '%%'
		or UPPER(o1.name) like '%%'
		or UPPER(c1.name) like '%%')]]
 order by TABLE_WITH_FK, PK_TABLE,constraint_column_id 
";

	const String FK_SQLTEMPLATE_2005 = @"
select convert(sysname,case when sc2.name='dbo' then o2.name else sc2.name+'.'+o2.name end) as TABLE_WITH_FK,
        convert(sysname,c2.name) as FK_FIELD,
        convert(sysname,case when sc1.name='dbo' then o1.name else sc1.name+'.'+o1.name end) as PK_TABLE,
        convert(sysname,c1.name) as PKEY,
        OBJECT_NAME(k.constraint_object_id) as FK_NAME,
        case f.is_disabled when 1 then 'Y' else '' end as DISABLED,
        case f.is_not_trusted when 1 then '* N' else 'Y' end as TRUSTED,
        case f.delete_referential_action when 0 then 'NO ACTION' WHEN 1 THEN 'CASCADE' WHEN 2 THEN 'SET_NULL' end as DELETE_RULE,
        case f.update_referential_action when 0 then 'NO ACTION' WHEN 1 THEN 'CASCADE' WHEN 2 THEN 'SET_NULL' end as UPDATE_RULE,
        case f.is_not_for_replication when 1 then 'Y' else 'N' end as REPLICATION_SAFE
 from   sys.foreign_keys f 
 join   sys.foreign_key_columns k on k.constraint_object_id = f.object_id
 join   sys.objects o1 on o1.object_id = f.referenced_object_id
 join   sys.objects o2 on o2.object_id = f.parent_object_id
 join   sys.columns c1 on c1.object_id = f.referenced_object_id and 
                          c1.column_id = k.referenced_column_id
 join   sys.columns c2 on c2.object_id = f.parent_object_id and 
                          c2.column_id = k.parent_column_id
left join sys.schemas sc1 on sc1.schema_id = o1.schema_id
left join sys.schemas sc2 on sc2.schema_id = o2.schema_id
	 [[ where (UPPER(case when sc2.name='dbo' then o2.name else sc2.name+'.'+o2.name end) like '%%' 
		or UPPER(c2.name) like '%%'
		or UPPER(case when sc1.name='dbo' then o1.name else sc1.name+'.'+o1.name end) like '%%'
		or UPPER(c1.name) like '%%')]]
 order by TABLE_WITH_FK, PK_TABLE,constraint_column_id 
";

	const String INDEX_SQLTEMPLATE_2005 = @"
select o.type, TableName, 
       i.name, i.type_desc, i.is_primary_key, i.is_unique,
       i.is_hypothetical, i.is_disabled, i.allow_row_locks, i.allow_page_locks
  from sys.indexes i
  join (select o.object_id,
			  o.type, 
              case when s.name = 'dbo' 
                   then o.name 
                   else s.name+'.'+o.name end as [TableName],
              case when s.name = 'dbo' 
                   then 1
                   else 0 end as isDbo
         from sys.objects o
    left join sys.schemas s on s.schema_id = o.schema_id
        ) o on o.object_id = i.object_id
 where o.type in ('U','V')
      [[ and (UPPER(TableName) like '%%' 
          or UPPER(i.name) like '%%')]]
order by o.type, isDbo desc, TableName, 1-i.is_primary_key
";

	// not right yet
	const String INDEX_SQLTEMPLATE = @"
select o.Type,
       OBJECT_NAME(i.id) as TableView,
       i.name,
       case when i.status = 18450 then 'Y' else '' end as 'PKey'
 from sysindexes i
 join sysobjects o on o.id = i.id
where o.name != i.name
 and o.type in ('U','V')
 and i.name not like '_WA_%'
order by 1,2,i.status desc
";

		const String JOBS_SQLTEMPLATE = @"
select j.name as Job_Name, 
       j.enabled, 
       j.job_id as [Job Id], 
       a.last_start_date as Last_Run, 
       j.description as Description
  from msdb.dbo.sysjobs_view j
left join (select job_id, MAX(start_execution_date) as last_start_date
             from msdb.dbo.sysjobactivity 
         group by job_id
		  ) a on a.job_id = j.job_id
 where UPPER(REPLACE(j.name,'_','')) like '%{0}%' or UPPER(j.description) like '%{0}%' 
order by UPPER(REPLACE(j.name,'_',''))
";

		const String JOBSTEPS_SQLTEMPLATE = @"
SELECT step_id,
       step_name,
       subsystem,
       command,
       output_file_name,
       last_run_duration,
       last_run_date,
       last_run_time,
       last_run_outcome
FROM msdb.dbo.sysjobsteps
WHERE (job_id ='{1}' )
  and upper(command) like '%{0}%'
order by job_id, step_id  
";

		const String JOBHISTORY_SQLTEMPLATE = @"
select h.job_id, 
		    CONVERT(DATETIME, RTRIM(run_date)) + (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4 as run_datetime,
			h.step_id,
			h.run_duration, 
			case h.run_status when 0 then 'Fail' when 1 then 'OK' when 2 then 'Retry' when 3 then 'Canceled' when 4 then 'Running' 
			                  else '???' end as STATUS,
			case when h.step_id = 0 then 'Job Finished' else s.step_name end as step_name,
		    h.message
	   from msdb..sysjobhistory h 
  left join msdb.dbo.sysjobsteps s on s.job_id = h.job_id and s.step_id = h.step_id
       where h.job_id = '{1}'
	     and (UPPER(s.step_name) like '%{0}%' or UPPER(h.message) like '%{0}%')
-- sort by date, then hour, then job step id (sorting 0=finished to the end
order by run_date desc, run_time/1000 desc, case when h.step_id=0 then 999 else h.step_id end
";

		const String DEPENDENCY_SQLTEMPLATE = @"
select distinct o.name as 'Dependent',
                v1.name as 'Type', 
                o2.name as 'Depends_On',
                v2.name as 'Type2'
  from sysdepends d
  join sysobjects o on d.id = o.id
  join sysobjects o2 on d.depid = o2.id
  join master.dbo.spt_values v1 on o.type = substring(v1.name,1,2) 
                                 collate database_default and v1.type = 'O9T'
  join master.dbo.spt_values v2 on o2.type = substring(v2.name,1,2) 
                                 collate database_default and v2.type = 'O9T'
 where d.id != d.depid            -- to ignore tables with computed columns
   and (upper(o2.name) like '%{0}%' or upper(o.name) like '%{0}%')
   and UPPER(o.name) not like 'MSMERGE%' -- filter replication objects.
   {1} -- exact match on selected name
order by v1.name desc, o.name,o2.name
";

		const String DEPENDENCY_SQLTEMPLATE_2005 = @"
select * from 
(
select distinct case when s.name='dbo' then o.name else s.name+'.'+o.name end as 'Dependent',
                v1.name as 'Type', 
                case when s2.name='dbo' then o2.name else s2.name+'.'+o2.name end as  'Depends_On',
                v2.name as 'Type2'
  from sysdepends d
  join sys.objects o on d.id = o.object_id
  join sys.objects o2 on d.depid = o2.object_id
  left join sys.schemas s on s.schema_id = o.schema_id
  left join sys.schemas s2 on s2.schema_id = o2.schema_id
  join master.dbo.spt_values v1 on o.type = substring(v1.name,1,2) 
                                 collate database_default and v1.type = 'O9T'
  join master.dbo.spt_values v2 on o2.type = substring(v2.name,1,2) 
                                 collate database_default and v2.type = 'O9T'
 where d.id != d.depid            -- to ignore tables with computed columns
   {1} -- exact match on selected name 
   and UPPER(o.name) not like 'MSMERGE%' -- filter replication objects.
) sq
 where upper(Dependent) like '%{0}%' or upper(Depends_On) like '%{0}%' 
order by Type, Dependent, Depends_On
";

//        const String AGGREGATE_SQLTEMPLATE = @"
//select top {3} 
//    NULLIF([{0}],'{4}') as [{0}], 
//    count([{0}]) as [COUNT] 
//from ( select ISNULL([{0}],'{4}') as [{0}] 
//       from {1} with (nolock)
//      where ISNULL([{0}],'{4}') like '%{2}%' COLLATE SQL_Latin1_General_CP1_CI_AS {5}
//     ) sq 
//group by [{0}] 
//order by count([{0}]) desc, [{0}]
//";

		const String AGGREGATE_SQLTEMPLATE = @"
select  [{0}],[COUNT], CAST((100.0 * [COUNT]/TOTAL) as Decimal(6,2)) AS [%] 
from (
select top {3} 
    NULLIF([{0}],'{4}') as [{0}], 
    count([{0}]) as [COUNT] 
from ( select ISNULL([{0}],'{4}') as [{0}] 
       from {1} with (nolock)
      where 1=1 {5}
        and ISNULL([{0}],'{4}') like '%{2}%' COLLATE SQL_Latin1_General_CP1_CI_AS -- Row Filter
     ) sq 
group by [{0}] 
) sq2
CROSS JOIN (SELECT COUNT(*) AS TOTAL FROM {1} with (nolock) where 1=1 {5}) t
order by [COUNT] desc, [{0}]
";



//select Color, [Count],
//       CAST((100.0 * [Count]/tot) as Decimal(6,2)) AS [%]
// from 
//(
//select top 1000  NULLIF([Color],'`') as [Color], 
//                 count([Color]) as [COUNT] 
//from ( select ISNULL([Color],'`') as [Color] 
//       from Production.Product with (nolock)
//      -- where ISNULL([Color],'`') like '%%' COLLATE SQL_Latin1_General_CP1_CI_AS 
//     ) sq 
//group by [Color] 
//) sq2
//CROSS JOIN (SELECT COUNT(*) AS TOT FROM Production.Product ) t
//order by [COUNT] desc, [Color]



		const String AGGREGATE_SQLTEMPLATE_ALT = @"
select top {3} 
    [{0}], 
    count([{0}]) as [COUNT] 
from ( select [{0}] 
       from {1}
     ) sq 
group by [{0}] 
order by count([{0}]) desc, [{0}] -- Can't search {2}
";

		const String COUNT_SQLTEMPLATE = @"
        select '{2}' as [{0}], 
               count([{0}]) as [COUNT] from {1} 
         where UPPER([{0}]) like '%{2}%' {3}
        ";

		const String MINMAX_SQLTEMPLATE = @"
        select '{2}' as [{0}], 
               min([{0}]) as [MIN], 
               max([{0}]) as [MAX] from {1} 
         where UPPER([{0}]) like '%{2}%' {3}
        ";

		const String AVG_SQLTEMPLATE = @"
        select '{2}' as [{0}], 
               sum(cast ([{0}] as float)) as [SUM], 
               avg(cast([{0}] as float)) as [AVERAGE] from {1} 
         where UPPER([{0}]) like '%{2}%' {3}
        ";

		const String LONGEST_SQLTEMPLATE_OLD = @"
        select '{2}' as [{0}], 
               min(len([{0}])) as [Shortest], 
               max(len([{0}])) as [Longest] from {1} 
         where UPPER([{0}]) like '%{2}%' {3}
        ";

		const String LONGEST_SQLTEMPLATE = @"
select DataLength, {0} from 
(
    select top 10 DataLength, {0}
      from 
      ( select min(len(rtrim({0}))) as DataLength 
          from {1} with (nolock)
         where UPPER({0}) like '%{2}%' {3}
      ) sh
      join {1} d with (nolock) on len(rtrim({0})) = sh.DataLength
    where UPPER({0}) like '%{2}%' {3}
    union         
    select top 10 DataLength, {0}
      from 
      ( select max(len(rtrim({0}))) as DataLength 
          from {1} with (nolock)
         where UPPER({0}) like '%{2}%' {3}
      ) sh
      join {1} d with (nolock) on len(rtrim({0})) = sh.DataLength
     where UPPER({0}) like '%{2}%' {3}
) sq
order by DataLength
";

		const String PERMISSIONS_SQLTEMPLATE = @"
if object_id('tempdb..#t') is not null drop table #t

create table #t (
Owner nvarchar(128),
Object nvarchar(128),
Grantee nvarchar(128),
Grantor nvarchar(128),
ProtectType nvarchar(128),
[Action] nvarchar(128),
[Column] varchar(1024))

insert into #t
 exec sp_helprotect

select so.Type, Owner, Object, Grantee, [Action], [Column],  Grantor, ProtectType
from #t t
left join sysobjects so on so.id = Object_id(Object)
where ISNULL(Owner,'') != 'sys'
 [[ and (UPPER(Object) like '%%' 
		or UPPER(so.Type) like '%%'
		or UPPER(Grantee) like '%%'
		or UPPER([Action]) like '%%')]]
order by case so.type when 'U' then '0' when 'V' then '1' when 'P' then '2' else ISNULL(so.type,'z') end,
Object, Grantee, [Column] 
";

		const String PERMISSIONS_SQLTEMPLATE_2005 = @"
-- mimic output od sp_helprotect
if object_id('tempdb..#t') is not null drop table #t

create table #t (
Owner nvarchar(128),
Object nvarchar(128),
Grantee nvarchar(128),
Grantor nvarchar(128),
ProtectType nvarchar(128),
[Action] nvarchar(128),
[Column] varchar(1024))

insert into #t
 exec sp_helprotect

alter table #t add CS varchar(1024)

-- count permissions on each object. Only show on 1st entry
update #t set CS = case when [Column] in ('(All)', '(All+New)','.') then 'All' else [Column] end


select so.Type, Owner, Object, Grantee,
SUBSTRING((SELECT ',' + s.[Action] FROM #t s 
           where s.Object=t.Object and s.Grantee = t.Grantee and s.[CS] = t.[CS]
           ORDER BY s.[Action] FOR XML PATH('')),2,200000) AS Actions,
 CS as [Columns],  Grantor, ProtectType
from #t t
left join sysobjects so on so.id = Object_id(Object)
where ISNULL(Owner,'') != 'sys'
 [[ and (UPPER(Object) like '%%' 
		or UPPER(so.Type) like '%%'
		or UPPER(Grantee) like '%%'
		or UPPER([Action]) like '%%')]]
group by so.Type, Owner, Object, Grantee, CS, Grantor, ProtectType
order by case so.type when 'U' then '0' when 'V' then '1' when 'P' then '2' else ISNULL(so.type,'z') end,
Object, Grantee, CS

";

		const String USERS_TEMPLATE= @"
	SELECT pp.type_desc,pp.name as role, p.name as member,  pp.is_fixed_role, l.name as Login_Account, isntuser as IsNT
	  FROM sys.database_role_members roles
	  JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
	  JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
 left join sys.syslogins l on l.sid = p.sid
	[[ where (UPPER(p.name) like '%%' or UPPER(pp.name) like '%%') ]]
  order by pp.name, p.type_desc, p.name
";

		const String LOCKS_SQLTEMPLATE = @"

	        select 	convert (smallint, req_spid) As spid,    
                hostname,
		        rsc_dbid As dbid,
                db_name(rsc_dbid) as dbname,
		        rsc_objid As ObjId,
                Object_name(rsc_objid) as ObJName,
		        rsc_indid As IndId,
		        substring (v.name, 1, 4) As Type,
		        substring (rsc_text, 1, 32) as Resource,
		        substring (u.name, 1, 8) As Mode,
		        substring (x.name, 1, 5) As Status

	        from 	master.dbo.syslockinfo li
            join    master.dbo.sysprocesses p on li.req_spid = p.spid
	        join 	master.dbo.spt_values v on li.rsc_type = v.number and v.type = 'LR'
            join    master.dbo.spt_values x on li.req_status = x.number	and x.type = 'LS'
	        join 	master.dbo.spt_values u on li.req_mode + 1 = u.number and u.type = 'L'
            where 1 = 1
               and not(rtrim(substring (u.name, 1, 8))='S' and substring (x.name, 1, 5)='GRANT') -- harmless S locks?
  --           and not(rtrim(substring (u.name, 1, 1))='S' and substring (x.name, 1, 5)='GRANT') -- harmless Sxxx locks?
             and not (db_name(rsc_dbid) = 'tempdb') -- no tempdb locks
             [[and (UPPER(db_name(rsc_dbid)) like '%%']] [[or UPPER(Object_name(rsc_objid)) like '%%')]]
            order by dbid, Object_name(rsc_objid)";
            
		const String PROCESSES_SQLTEMPLATE = @"

        select p.spid,
               case when lower(p.status)  = 'sleeping' 
                     and  upper(p.cmd)    in (
                             'AWAITING COMMAND'
                            ,'LAZY WRITER'
                            ,'CHECKPOINT SLEEP')
                     and  blocked = 0
                    then 0 else 1 end as active,
               p.status,
	           blk=convert(char(5),blocked),
	           dbname = case
					        when p.dbid = 0 then null
					        when p.dbid <> 0 then db_name(p.dbid)
				        end,
        	   
	           p.hostname,
               loginame=rtrim(p.loginame),
	           p.cmd,
               p.program_name,
               t.text as last_command
        from  master.dbo.sysprocesses p
		cross apply sys.dm_exec_sql_text(sql_handle) t
        where p.spid >= 0 and p.spid <= 32767
        [[and (UPPER(db_name(p.dbid)) like '%%']] [[or UPPER(p.hostname) like '%%')]]
        order by db_name(p.dbid), p.hostname";

		const String SERVICEBROKER_SQLTEMPLATE = @"
select s.name as Service, m.name as Message, q.name as Queue, q.activation_procedure as ProcedureName,  c.name as Contract
 from sys.service_contracts c
 left join sys.service_contract_usages u on u.service_contract_id = c.service_contract_id
 left join sys.service_contract_message_usages mu on mu.service_contract_id = c.service_contract_id
 left join sys.service_message_types m on m.message_type_id = mu.message_type_id
 left join sys.services s on s.service_id = u.service_id
 left join sys.service_queue_usages qu on qu.service_id = s.service_id
 left join sys.service_queues q on q.object_id = qu.service_queue_id
where q.activation_procedure is not NULL
   [[ and (UPPER(q.activation_procedure) like '%%' 
   or UPPER(m.name) like '%%' 
   or UPPER(c.name) like '%%' 
   or UPPER(q.name) like '%%' 
   or UPPER(s.name) like '%%' ) ]]
 order by s.name,q.name,m.name";
 
	const String PUBLICATIONS_TEMPLATE = @"
-- publications
if object_id('syspublications') is not null
	select 	p.name as Publication_Name,
			count(a.artid) as items,
			s.srvname +'.' + s.dest_db as Subscriber
	  from syspublications p
	  join sysarticles a on a.pubid = p.pubid
	  join syssubscriptions s on s.artid = a.artid
	 [[ where UPPER(p.name) like '%%' ]]
	group by  p.name, s.srvname, s.dest_db
	order by  p.name, s.srvname, s.dest_db
else
	select 'Replication not enabled on this database' as Publication_Name, NULL as items, NULL as Subscriber
";

	const String REPLARTICLES_TEMPLATE = @"
if object_id('syspublications') is not null
	-- pubs, subs and tables.
	select distinct p.name as Publication_Name,
		   a.name as [Table]
	  from syspublications p
	  join sysarticles a on a.pubid = p.pubid
	 [[ where (UPPER(p.name) like '%%' 
		or UPPER(a.name) like '%%') ]]
	 {0} -- exact match on publication name
	order by p.name, a.name
else
	select 'Replication not enabled on this database' as Publication_Name, NULL as [Table]
";

	const String FIELDDESCRIPTIONS_TEMPLATE_OLD = @"
select c.name as [Field Name],
       m.value as [Description]
  from syscolumns c
left join ( SELECT objname, value 
              FROM ::fn_listextendedproperty('MS_Description', 'user', 'dbo', 'table', '{0}', 'column', default)
          ) m on m.objname = c.name collate database_default
 where id = OBJECT_ID('{0}')
 [[ and (UPPER(c.name) like '%%' 
 	 or UPPER(cast(m.value as varchar(max))) like '%%')]]
";
	const String FIELDDESCRIPTIONS_TEMPLATE = @"
select cols.name as [Field Name], 
	   p.value as [Description]
  from sys.columns cols
left join sys.extended_properties p on p.major_id = cols.object_id and p.minor_id = cols.column_id 
							        and class = 1 and p.name = 'MS_Description' 
where cols.object_id = OBJECT_ID('{0}')
 [[ and (UPPER(cols.name) like '%%' 
 	 or UPPER(cast(p.value as varchar(max))) like '%%')]]
order by   cols.column_id";

	const String DEPENDTREE_TEMPLATE = @"
    WITH Obj AS (
        SELECT DISTINCT
            s.id  AS ParentID,
            s.DepID AS ObjectID,
            o1.Name AS ParentName,
            o2.Name AS ChildName,
            o1.Name AS ParentObject, 
            o2.Name AS ObjectName
        FROM sys.sysdepends s
        INNER JOIN sys.all_objects o1 ON s.id = o1.object_id
        INNER JOIN sys.schemas sch1 ON sch1.schema_id = o1.schema_id
        INNER JOIN sys.all_objects o2 on s.DepID = o2.object_id
        INNER JOIN sys.schemas sch2 ON sch2.schema_id = o2.schema_id
        where s.id != s.DepID
    ), 
    cte AS (
        SELECT 
            0 AS lvl, 
            ParentID,
            ObjectID,
            ParentObject,
            ObjectName,
            CAST(ObjectID AS VARBINARY(512)) AS Sort
        FROM Obj WHERE ParentName = '{0}'
        UNION ALL 
        SELECT
            p.lvl+ 1, 
            c.ParentID,
            c.ObjectID,
            c.ParentObject,
            c.ObjectName,
            CAST(p.Sort + CAST(c.ObjectID AS VARBINARY(16)) AS VARBINARY(512))
        FROM cte p 
        INNER JOIN Obj c ON p.ObjectID = c.ParentID
    )
    SELECT  REPLICATE('|  ',(lvl * 1)) + '|- ' + ObjectName 
    FROM cte
    ORDER BY Sort
   
";

const String LINKEDSERVER_TEMPLATE = @"
	select srv.name, srv.data_source, srv.catalog, 
			srv.is_data_access_enabled as DataAcess,
			srv.is_linked as Linked,
			srv.is_remote_login_enabled as RemoteLogin,
			srv.is_rpc_out_enabled as RpcOut, 
			srv.provider, srv.product, 
			srv.provider_string, srv.location,
			srv.is_system as IsSys
	  from sys.servers srv
	 [[ where (UPPER(srv.name) like '%%' 
		or UPPER(srv.data_source) like '%%'
		or UPPER(srv.catalog) like '%%')
		]]
     order by srv.is_system, srv.data_source, srv.catalog
";

const String PROPSUMMARY_TEMPLATE = @"
if object_id('tempdb..#t_wip') is not null drop table #t_wip
select 	p.class, 
		case when p.class = 1 then 
 			 case when p.minor_id > 0
				  then 'COLUMN' 
				  else case when o.type_desc is null then 
							case when t.name is not null  
								 then 'DDL_TRIGGER'
								 else 'UNKNOWN'
								 end
							else o.type_desc
							end
				  end
			 else p.class_desc
			 end as 'Item', 
	    o.type,
		p.name as Property_Name
 into #t_wip
 from sys.extended_properties p
left join sys.objects o on o.object_id = p.major_id
left join sys.triggers t ON p.major_id = t.object_id


select  item, Property_Name, count(class) as Item_Count from #t_wip
[[ where (UPPER(name) like '%%' ]]
group by class, item, Property_Name
order by class, case item  when 'USER_TABLE' then '_A' when 'COLUMN' then 'A' else item end
";

	const String TABLE_SIZE_TEMPLATE_OLD = @"
if object_id('tempdb..#t') is not null drop table #t
create table #t (name SYSNAME, rows CHAR(11), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))
EXEC sp_MSforeachtable 'INSERT INTO #t EXEC sp_spaceused ''?'''
select name,
       rows,
       cast(cast(replace(reserved, ' KB', '') as float)/(1024*1024) as decimal(16, 3)) as reserved_in_GB,
       cast(cast(replace(reserved, ' KB', '') as float)/(1024) as decimal(16, 3)) as reserved_in_MB,
       cast(cast(replace(reserved, ' KB', '') as float)/(1) as decimal(16, 0)) as reserved_in_KB,
       cast(cast(replace(data, ' KB', '') as float)/(1) as decimal(16, 0)) as data_in_KB,
       cast(cast(replace(index_size, ' KB', '') as float)/(1) as decimal(16, 0)) as index_size_in_KB,
       cast(cast(replace(unused, ' KB', '') as float)/(1) as decimal(16, 0)) as unused_in_KB
  from #t
	 [[ where (UPPER(name) like '%%') ]]
order by cast(cast(replace(reserved, ' KB', '') as float)/(1) as decimal(16, 0)) desc
";
// new template: faster? plus shows schema
// replace with above if queries on results.
const String TABLE_SIZE_TEMPLATE = @"
	/*
		Shamelessly adapted from sp_spacedused table size portion
		Calculate the summary data. 
	*/
	if object_id('tempdb..#space') is not null drop table #space
	SELECT 
		object_id,
		SUM (s.reserved_page_count) as reservedpages,
		SUM (s.used_page_count) as usedpages,
		SUM (CASE WHEN (s.index_id < 2) 
                  THEN (s.in_row_data_page_count + s.lob_used_page_count + s.row_overflow_used_page_count)
				  ELSE s.lob_used_page_count + s.row_overflow_used_page_count
			  END ) as pages,
		SUM (CASE WHEN (s.index_id < 2) THEN s.row_count
				  ELSE 0
		      END ) as [rowCount],
		cast(NULL as decimal(16,3)) as reservedpages_GB,
		cast(NULL as decimal(16,3)) as reservedpages_MB,
		cast(NULL as bigint) as index_size,
		cast(NULL as bigint) as unused
	into #space
	FROM sys.dm_db_partition_stats s
	GROUP BY object_id

	/*
		add overflow data from eg Xml indexes and Full text index 
	*/	
	update s set reservedpages = s.reservedpages + i.reservedpages,
	  			 usedpages = s.usedpages + i.usedpages
	from #space s
    join (select i.parent_id,
                 reservedpages, 
                 usedpages 
	        from #space s
 			join sys.internal_tables i on i.object_id = s.object_id
           where i.internal_type IN (202,204,211,212,213,214,215,216)
		 ) i on i.parent_id = s.object_id

	/*
		scale the reserved update the figures to make them presentable
	*/
	-- pages to KB.
	update #space set reservedpages_MB = reservedpages * 8,
					  reservedpages_GB = reservedpages * 8,
					  reservedpages = reservedpages * 8,
					  usedpages = usedpages * 8,
					  pages = pages * 8
	-- large scale figures and other calculations
	update #space set reservedpages_MB = reservedpages_MB/1024,
					  reservedpages_GB = reservedpages_GB/(1024 * 1024),
					  index_size = CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END,
				  	  unused = CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END

--select * from #space

select Name, Rows, Reserved_in_GB, Reserved_in_MB, Reserved_in_KB,
	   Data_in_KB, Index_size_in_KB, Unused_in_KB
from (
	SELECT case when sc.name='dbo' then o.name 
		 		else sc.name+'.'+o.name end as Name,
		   s.[rowCount] as Rows,
		   reservedpages_GB as Reserved_in_GB, 
		   reservedpages_MB as Reserved_in_MB,
		   reservedpages as Reserved_in_KB,
		   pages as Data_in_KB,
		   index_size as Index_size_in_KB,
		   unused as Unused_in_KB
     from #space s
     join sys.OBJECTS o on o.object_id = s.object_id
left join sys.schemas sc on sc.schema_id = o.schema_id
    where o.type = 'U'
) qry
	 [[ where (UPPER(Name) like '%%') ]]
order by Reserved_in_KB desc, Data_in_KB desc, Rows desc, Name
";

		// Used bby database information
		// Credit to  http://blog.sqlauthority.com/2010/11/12/sql-server-get-all-the-information-of-database-using-sys-databases/
		// and the guy mentioned who posted the script
	public const String DBINFO_TEMPLATE = @"
SELECT 	database_id,
		CONVERT(VARCHAR(25), DB.name) AS dbName,
		CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [status],
		state_desc,
		(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS dataFiles,
		(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [data mb],
		(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,
		(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [log mb],
		user_access_desc AS [user access],
		CASE compatibility_level WHEN 60 THEN '60 (SQL Server 6.0)'
								WHEN 65 THEN '65 (SQL Server 6.5)'
								WHEN 70 THEN '70 (SQL Server 7.0)'
								WHEN 80 THEN '80 (SQL Server 2000)'
								WHEN 90 THEN '90 (SQL Server 2005)'
								WHEN 100 THEN '100 (SQL Server 2008)'
								WHEN 110 THEN '110 (SQL Server 2012)' -- a guess
								END AS [compatibility level],
		case when charindex(cast(DATABASEPROPERTYEX(name, 'Collation') as varchar(120)), '_CS_') > 0 then 'Yes' 
			when UPPER(cast(DATABASEPROPERTYEX(name, 'Collation') as varchar(120))) = 'LATIN1_GENERAL_BIN' then 'Yes'
		    else 'No' end as [case sensitive],
		case when is_broker_enabled = 1 then 'Yes' else 'No' end as [service broker],
		case when is_trustworthy_on = 1 then 'Yes' else 'No' end as [set trustworthy],
		case when is_master_key_encrypted_by_server = 1 then 'Yes' else 'No' end as [key encrypted],
		CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [creation date],
		-- last backup
		recovery_model_desc AS [recovery model],
		ISNULL((SELECT TOP 1
						CASE type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' - ' +
						LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),backup_finish_date))) + ' days ago', 'NEVER')) + ' - ' +
						CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' - ' +
						CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +
						' (' + CAST(DATEDIFF(second, BK.backup_start_date, BK.backup_finish_date) AS VARCHAR(4)) + ' '+ 'seconds)'
				FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [last backup],
		CASE WHEN is_fulltext_enabled = 1 THEN 'Yes' ELSE 'No' END AS [fulltext],
		CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose],
		page_verify_option_desc AS [page verify option],
		CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [read only],
		CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink],
		CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto create statistics],
		CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto update statistics],
		CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [standby],
		CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE '' END AS [cleanly shutdown],
		cast(DATABASEPROPERTYEX(name, 'Collation') as varchar(120)) as [collation]
 FROM sys.databases DB
where name = '{0}' COLLATE SQL_Latin1_General_CP1_CI_AS 
";
	public const String DBFILEINFO_TEMPLATE = @"
--
-- get free space from known drives
--
declare @t table (drive char(1), mb_free int)
insert into @t
exec master.sys.xp_fixeddrives

select  f.name as [File Name],
        (f.size*8)/1024 as [File Size(mb)], 
        case when f.type = 0 then cast(cast(mb_free as float)/nullif((f.size*8)/1024, 0) as decimal(15,2)) 
                             else NULL end as [Spare factor], 
		case when f.type = 0 then tot_size_mb else NULL end as [Data + Log(mb)],
        case when f.type = 0 then db.name else '' end as [Database],
        case when f.type = 0 then f.state_desc else NULL end as State,
  		mb_free as [Disk free(mb)],
		f.physical_name as [Path]
  from sys.master_files f
  join (select database_id, (sum(size)*8/1024) as tot_size_mb from sys.master_files f group by database_id) tot on tot.database_id = f.database_id
  join sys.databases db on db.database_id = f.database_id
  join @t sp on sp.drive = LEFT(physical_name,1) COLLATE SQL_Latin1_General_CP1_CI_AS

order by tot_size_mb desc, f.database_id, f.type, f.physical_name
";
	}
}
